<?php
include_once('ConnectionManager.php');
	
function findAllUsersBy($keyword, $startPosition, $rowPerPage) {
    $query = "SELECT U.*, (SELECT COUNT(*) FROM GIAAN_RECEIPT R WHERE R.USER_ID = U.USER_ID) AS nReceipts FROM GIAAN_USER U ";
	$where = "";
	if (isset($keyword)) $where .= " WHERE U.USER_FULLNAME LIKE '%".htmlspecialchars($keyword, ENT_QUOTES)."%' OR U.USER_NAME LIKE '%".htmlspecialchars($keyword, ENT_QUOTES)."%' ";
	$orderBy = " ORDER BY U.USER_NAME LIMIT $startPosition, $rowPerPage";
    ConnectionManager::getInstance()->setQuery($query.$where.$orderBy);
    return ConnectionManager::getInstance()->query();
}

function countUserBy($keyword){
	$query = "SELECT COUNT(USER_ID) FROM GIAAN_USER WHERE USER_FULLNAME LIKE '%".htmlspecialchars($keyword, ENT_QUOTES)."%' OR USER_NAME LIKE '%".htmlspecialchars($keyword, ENT_QUOTES)."%'";
	ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
	return ConnectionManager::getInstance()->getNumRows();
}

function insertUser($username, $password, $type, $fullName, $dob, $email, $phoneNumber){
	$dobValue = "";	
	if($dob != "") $dobValue = "str_to_date('$dob', '%d/%m/%Y')";
	else $dobValue = "NULL";
	
	$query = "INSERT INTO GIAAN_USER(USER_NAME, USER_PASS, USER_TYPE, USER_FULLNAME, USER_DOB, USER_EMAIL, USER_PHONENUMBER, USER_CREATEDATE) VALUES 
	                          ('$username', '$password', $type, '$fullName', $dobValue , '$email', '$phoneNumber', str_to_date(CURDATE(), '%Y-%m-%d'))";			  
	ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
	if(mysql_affected_rows() > 0) return true;
	return false;
}

function authenticateUser($username, $password, $type){
	$query = "SELECT USER_NAME FROM GIAAN_USER WHERE USER_TYPE = $type AND USER_NAME = '".htmlspecialchars($username, ENT_QUOTES)."' and USER_PASS = '".htmlspecialchars($password, ENT_QUOTES)."'";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
	return ConnectionManager::getInstance()->getNumRows() > 0;
}

function findUserBy($username){
	$query = "SELECT * FROM GIAAN_USER WHERE USER_NAME = '$username'";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function updatePassword($userId, $newPass){
	$query = "UPDATE GIAAN_USER SET USER_PASS = '$newPass' WHERE USER_ID = $userId";
	ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function findUserDetail($userId){
	$query = "SELECT * FROM GIAAN_USER WHERE USER_ID = $userId";
	ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function isExistedUsername($username){
	$query = "SELECT COUNT(USER_ID) as count FROM GIAAN_USER WHERE USER_NAME = '$username'";
	ConnectionManager::getInstance()->setQuery($query);
    $row = ConnectionManager::getInstance()->fetch(ConnectionManager::getInstance()->query());
	if($row["count"] == "0")
		return false;
	return true;
}

function editUser($username, $fullName, $dob, $email, $phoneNumber, $password){
	$dobValue = "";	
	if($dob != "") $dobValue = "str_to_date('$dob', '%d/%m/%Y')";
	else $dobValue = "NULL";
	
	$query = "UPDATE GIAAN_USER SET USER_FULLNAME = '$fullName', USER_DOB = $dobValue, USER_EMAIL = '$email', USER_PHONENUMBER = '$phoneNumber', USER_PASS = '$password' WHERE USER_NAME = '$username'";
	ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
	if(mysql_affected_rows() > 0)
		return true;
	return false;
}

function countAllUsers(){
	$query = "SELECT COUNT(*) AS NUM FROM GIAAN_USER WHERE USER_TYPE <> 1";
	ConnectionManager::getInstance()->setQuery($query);
	return ConnectionManager::getInstance()->fetch(ConnectionManager::getInstance()->query());
}
?>